/**
 * 
 */
package org.swing.utility.jdbc.helper;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Date;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * @author lqnhu
 *
 */
public class SqlUtil {

	public static class PreparedStatementBuilder {
		PreparedStatement ps;
		int pos = 1;
		StringBuilder log = new StringBuilder();

		public PreparedStatementBuilder(PreparedStatement ps)
				throws SQLException {
			this.ps = ps;
		}

		public PreparedStatementBuilder(Connection conn, String sql)
				throws SQLException {
			this(conn.prepareStatement(sql));
			log.append(sql);
		}

		public PreparedStatementBuilder set(Long l) throws SQLException {
			ps.setLong(pos++, l);
			return this;
		}

		public PreparedStatementBuilder set(Integer i) throws SQLException {
			if (i != null) {
				ps.setInt(pos++, i);
			} else {
				ps.setNull(pos++, Types.INTEGER);
			}
			return this;
		}

		public PreparedStatementBuilder set(Double d) throws SQLException {
			if (d != null) {
				ps.setDouble(pos++, d);
			} else {
				ps.setNull(pos++, Types.DOUBLE);
			}
			return this;
		}

		public PreparedStatementBuilder set(String s) throws SQLException {
			ps.setString(pos++, s);
			return this;
		}

		public PreparedStatementBuilder set(Boolean b) throws SQLException {
			ps.setBoolean(pos++, b);
			return this;
		}

		public PreparedStatementBuilder set(Date d) throws SQLException {
			ps.setDate(pos++, d != null ? new java.sql.Date(d.getTime()) : null);
			return this;
		}

		public PreparedStatementBuilder set(byte[] b) throws SQLException {
			ps.setBytes(pos++, b);
			// ps.setBinaryStream(pos++, is);
			return this;
		}

		public PreparedStatement statement() {
			return ps;
		}
	}

	public static void runScript(InputStream stream, Connection cx)throws SQLException {
		BufferedReader reader = new BufferedReader(
				new InputStreamReader(stream));
		Statement st = cx.createStatement();
		try {
			StringBuilder buf = new StringBuilder();
			String sql = reader.readLine();
			while (sql != null) {
				sql = sql.trim();
				if (!sql.isEmpty() && !sql.startsWith("--")) {
					buf.append(sql).append(" ");
					if (sql.endsWith(";")) {
						String stmt = buf.toString();
						boolean skipError = stmt.startsWith("?");
						if (skipError) {
							stmt = stmt.replaceAll("^\\? *", "");
						}
						st.addBatch(stmt);
						buf.setLength(0);
					}
				}
				sql = reader.readLine();
			}
			st.executeBatch();
		} catch (IOException e) {
			throw new SQLException(e);
		} finally {
			try {
				stream.close();
			} catch (IOException e) {
			}
			try {
				st.close();
			} catch (SQLException e) {
			}
		}
	}

	private static final Pattern PROPERTY_PATTERN = Pattern.compile("\\$\\{(.+?)\\}");

	public static void runScript(InputStream stream, Connection cx,Map<String, String> properties) throws SQLException {
		BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
		Statement st = cx.createStatement();
		int insideBlock = 0;
		try {
			StringBuilder buf = new StringBuilder();
			String sql = reader.readLine();
			while (sql != null) {
				sql = sql.trim();
				if (!sql.isEmpty() && !sql.startsWith("--")) {
					buf.append(sql).append(" ");
					if (sql.startsWith("BEGIN")) {
						insideBlock++;
					} else if (insideBlock > 0 && sql.startsWith("END")) {
						insideBlock--;
					}
					if (sql.endsWith(";") && insideBlock == 0) {
						Matcher matcher = PROPERTY_PATTERN.matcher(buf);
						while (matcher.find()) {
							String propertyName = matcher.group(1);
							String propertyValue = properties.get(propertyName);
							if (propertyValue == null) {
								throw new RuntimeException("Missing property "
										+ propertyName + " for sql script");
							} else {
								buf.replace(matcher.start(), matcher.end(),
										propertyValue);
								matcher.reset();
							}
						}
						String stmt = buf.toString();
						st.addBatch(stmt);
						buf.setLength(0);
					}
				}
				sql = reader.readLine();
			}
			st.executeBatch();
		} catch (IOException e) {
			throw new SQLException(e);
		} finally {
			try {
				stream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static PreparedStatementBuilder prepare(Connection conn, String sql)
			throws SQLException {
		return new PreparedStatementBuilder(conn, sql);
	}

	public static PreparedStatementBuilder prepare(PreparedStatement st)
			throws SQLException {
		return new PreparedStatementBuilder(st);
	}
}
